﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using CarsModel;
using System.Data;
using System.Data.SqlClient;
using Lib.Data;

namespace CarsDal
{
    public  class GoodsDal
    {
        public static string ConnectionString = "Data Source=47.93.36.227;Initial Catalog=SecondHandCars;User ID=Yang;Pwd=qaz@WSXedc";
        SqlConnection con = new SqlConnection(ConnectionString);
        /// <summary>
        /// 卖家上传商品信息
        /// </summary>
        /// <param name="good">赋值之后的实体</param>
        /// <returns>返回数据库受影响行数</returns>
        public int AddGoods(Goods good)
        {
            string Sql = SqlADDString<Goods>.ADD(good);
            SqlCommand com = new SqlCommand(Sql,con);
            con.Open();
            int i = com.ExecuteNonQuery();
            con.Close();
            return i;
        }

        /// <summary>
        /// 管理员完善/修改卖家上传的信息
        /// </summary>
        /// <param name="good"></param>
        /// <returns></returns>
        public int GoodsUpdate(Goods good)
        {
            string sql = "update Goods set TypeID=" + good.TypeID + ",GoodsType='" + good.GoodsType + "',GoodsSet='" + good.GoodsSet + "',GoodsPaiDate='" + good.GoodsPaiDate + "',GoodsMileage='" + good.GoodsMileage + "',GoodsPai='" + good.GoodsPai + "',GoodsNewOrOld='" + good.GoodsNewOrOld + "',GoodsPrice=" + good.GoodsPrice + ",GoodsImage='" + good.GoodsImage + "',UsersID=" + good.UsersID + ",GoodsState=1,CarsModelsID="+good.CarsModelsID;
            SqlCommand com = new SqlCommand(sql,con);
            con.Open();
            int i = com.ExecuteNonQuery();
            con.Close();
            return i;
        }
        #region
        ///// <summary>
        ///// 商品前六条显示
        ///// </summary>
        ///// <returns></returns>
        //public List<Goods> GoodsList()
        //{
        //    string sql = "select * from (select *,ROW_NUMBER() over(order by (GoodsID))a  from Goods)b where b.a between 1 and 6";
        //    SqlCommand com = new SqlCommand(sql,con);
        //    SqlDataAdapter adapter=new SqlDataAdapter(com);
        //    DataTable dt=new DataTable();
        //    adapter.Fill(dt);
        //    adapter.Dispose();
        //    List<Goods> list = DataTableToList.DataTableToLista<Goods>(dt);
        //    return list;

        //}


        //public List<Goods> GoodsList()
        //{ 
        //    return L
        //}

        ///// <summary>
        ///// 卖家查看自己上架的商品
        ///// </summary>
        ///// <param name="Usersid">卖家ID</param>
        ///// <param name="PageIndex">当前页码</param>
        ///// <param name="PageSize">每页显示条数</param>
        ///// <returns></returns>
        //public List<Goods> GoodsListPage(int? Usersid,int? PageIndex,int? PageSize)
        //{
        //    StringBuilder str = new StringBuilder();
        //    str.Append(" select * from ");
        //    str.Append(" (select *,ROW_NUMBER() over (order by (GoodsID))a from Goods where 1=1  ");
        //    if (Usersid != null && Usersid != 0)
        //    { 
        //        str.Append(" and  UsersID=1 ");
        //    }
        //    str.Append( " )b ");
        //    if (PageIndex != null && PageIndex != 0 && PageSize != null && PageSize != 0)
        //    {
        //        str.Append(" where b.a between " + (PageIndex - 1) * PageSize + 1 + " and " + PageIndex * PageSize);
        //    }
        //    SqlCommand com = new SqlCommand(str.ToString(), con);
        //    SqlDataAdapter adapter = new SqlDataAdapter(com);
        //    DataTable dt = new DataTable();
        //    adapter.Fill(dt);
        //    adapter.Dispose();
        //    List<Goods> list = DataTableToList.DataTableToLista<Goods>(dt);
        //    return list;

        //}
        #endregion
        /// <summary>
        /// 商品显示
        /// </summary>
        /// <returns>所有商品</returns>
        public List<Goods> GoodsList()
        {
            string sql = "select * from Goods";
            SqlCommand com = new SqlCommand(sql,con);
            SqlDataAdapter adapter = new SqlDataAdapter(com);
            DataTable dt = new DataTable();
            adapter.Fill(dt);
            adapter.Dispose();
            List<Goods> list = DataTableToList.DataTableToLista<Goods>(dt);
            return list;
        }


        ///// <summary>
        ///// 查询商品总数
        ///// </summary>
        ///// <returns>返回商品总数</returns>
        //public int GoodsCount()
        //{
        //    string sql = "select Count(*) from Goods";
        //    SqlCommand com = new SqlCommand(sql,con);
        //    con.Open();
        //    int i =Convert.ToInt32( com.ExecuteScalar());
        //    con.Close();
        //    return i;
        //}
        /// <summary>
        /// 商品信息删除
        /// </summary>
        /// <param name="id">要删除的商品的主键ID</param>
        /// <returns>受影响行数</returns>
        public int GoodsDel(int id)
        {
            string sql = "delete * from goods where GoodsID="+id;
            SqlCommand com = new SqlCommand(sql,con);
            con.Open();
            int i = com.ExecuteNonQuery();
            con.Close();
            return i;
            
        }

    }
}
